IF OBJECT_ID('dbo.RptFederalFundedDeliverables') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptFederalFundedDeliverables
    IF OBJECT_ID('dbo.RptFederalFundedDeliverables') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptFederalFundedDeliverables >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptFederalFundedDeliverables >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptFederalFundedDeliverables
--
-- Author:      Chris Bernad, Parallax Consulting LLC
--
-- Create date: 12/4/2008
--
-- Description: Source of Report, "AwardedBudgetsCostSharing"
--                  In Insight DB Reporting Services
--
-- Parameters:  
--     @ProjectStartDate               
--     @ProjectEndDate                 
--     @Institution             
--     @Department              
--     @PrincipalInvestigator        
--     @SponsorType                 
--     @CostShareTypoe       
--     @domainUserId            
--              
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- 
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 12/4/08     CB        Created
-- =======================================================================
CREATE PROCEDURE dbo.RptFederalFundedDeliverables 
     (
     @StartDate				  DATETIME, 
     @EndDate				  DATETIME,
     @Institution             VARCHAR(100) = NULL,
     @Department              VARCHAR(200) = NULL,
     @PrincipalInvestigator   VARCHAR(2000) = NULL,          
     @Sponsor                 VARCHAR(2000) = NULL,
     @SponsorType			  VARCHAR(200) = NULL,
     @DomainUserId            INT
     ) 
                                   
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error INT,
               @RowCount INT
               
     -- ALL checking
     IF @Institution IN ('ALL','','0') SET @Institution = NULL
     IF @Department IN ('ALL','','0') SET @Department = NULL
     IF @PrincipalInvestigator IN ('ALL','','0') SET @PrincipalInvestigator = NULL
     IF @Sponsor IN ('ALL','','0') SET @Sponsor = NULL
     IF @SponsorType IN ('ALL','','0') SET @SponsorType = NULL
          
     -- Result Set
     DECLARE   @Proposal TABLE (InstCode varchar(10),
                                PropNo varchar(10))
     INSERT INTO @Proposal
     
     SELECT p.InstCode,p.PropNo
     FROM vwMasterProposalsInfo p
	     INNER JOIN INSIGHT_RPT_DB.dbo.fnGetSecurityAgreements(@DomainUserId) sec ON p.propNo = sec.InfoEdPropNumber
	 WHERE p.PropStat ='PARTNERS_PD_PROP_STAT_2' AND --Fully Executed/Awarded - Activated
	     (@Institution IS NULL OR  p.Institution IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@Institution))) AND
         (@Department IS NULL OR p.DeptartmentChiefCode IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@Department))) AND
         (@PrincipalInvestigator IS NULL OR p.PISearchName IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode( @PrincipalInvestigator ))) AND
         (@Sponsor IS NULL OR p.SponCode IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode( @Sponsor ))) AND
         (@SponsorType IS NULL OR p.SponsorTypeCode IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode( @SponsorType ))) 	     
     
    SELECT DISTINCT
     	p.InstCode,
		p.ProposalNumber,
		p.PropNo, 
		p.SponsorAgreementId,
		p.Institution, 
	    p.DeptartmentChiefCode,	p.Department,
        p.UnitChiefCode, p.Unit,
		p.PrincipalInvestigator,  
		p.SponsorName, p.SponsorType,
		d.ReportID,		
		d.ReportName as DeliverableName,
		ds.code_desc as DeliverableStatus,
		d.DueDate,
		d.CompletedDate,
		DATEDIFF(dd, d.DueDate, d.CompletedDate) AS DueVsRptDiff,
		CASE WHEN r2.lname IS NOT NULL THEN 
		 RTRIM(r2.lName) + ', ' + RTRIM(r2.fName) + RTRIM(IsNull(' ' + r2.mi,'')) 
					ELSE NULL END AS ReportedBy
	FROM vwMasterProposalsInfo p
	     INNER JOIN @Proposal pf ON p.PropNo = pf.PropNo
	     INNER JOIN ptDeliverable d ON pf.InstCode = d.inst_code AND pf.PropNo = d.prop_no
         LEFT OUTER JOIN codetab ds	ON d.reportstat = ds.codeID AND	ds.arr_name = 'REPORTSTAT'
		 LEFT OUTER JOIN Faculty r2 ON d.CompletedBy = r2.Unique_ID --submittedby
         INNER JOIN pt_acct a ON p.PropNo = a.prop_no AND p.InstCode = a.inst_code 
		 INNER JOIN projmain am ON am.projnumber = a.hacct_no AND am.system = 'pm' 
		 INNER JOIN projobject ao ON am.projid = ao.projid 
		 INNER JOIN pmaccount pm ON pm.instaccountno = a.hacct_no
		 INNER JOIN codetab ast ON ast.codeid = pm.account_st  AND ast.arr_name = 'Account_St'  
   WHERE d.DueDate BETWEEN @StartDate AND @EndDate AND 
         d.reportcat IN('PARTNERS_P_REPORTCAT_1','PARTNERS_P_REPORTCAT_23') AND --Financial Report,Final Financial Status Report
	     ast.code_desc <> 'Inactive' 
	      
ORDER BY p.Institution,p.ProposalNumber
    
  SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
  IF @Error!=0
     RETURN -1
      
  -- Return Success
  RETURN 0

END
GO

IF OBJECT_ID('dbo.RptFederalFundedDeliverables') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptFederalFundedDeliverables >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptFederalFundedDeliverables>>>'
GO

IF  EXISTS
    (SELECT name
     FROM sys.database_principals
     WHERE name = 'InsightReport')
BEGIN
	GRANT EXECUTE ON dbo.RptFederalFundedDeliverables TO InsightReport
END


GO

